package com.cs.paoyer.administration.business.entity;

//import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.HashMap;
//import java.util.Vector;
import java.sql.PreparedStatement;
//import java.util.Iterator;

import com.cs.paoyer.administration.business.model.ApplicationVO;
//import com.cs.paoyer.common.exception.UserException;
//import com.cs.paoyer.common.exception.SystemException;
//import com.cs.paoyer.common.util.Conversion;

/**
 * DAO para crear, consultar, actualizar y eliminar registros en la tabla TCS_APLICACION.
 * 
 * @author Nicolas.Ceron@consult-soft.com
 * @version 1.0
 */

public class ApplicationDAO 
{
  /**
   * Conexion a la base de datos.
   */
  private Connection conn = null;

  /**
   * Constructor que recibe como parametro la conexion con la base de datos.
   * @param conn Conexion con la base de datos.
   */
  
  public ApplicationDAO(Connection conn){
    this.conn = conn;
  }

  
    public Collection findApplicationByCriterea(ApplicationVO criterea) throws SQLException 
    {
    
      
				String query = "SELECT apl.codigo,"+
                       "       apl.nombre,"+
                       "       to_char(apl.desde_vigencia,'yyyy-mm-dd') dvig,"+
                       "       to_char(apl.hasta_vigencia,'yyyy-mm-dd') hvig,"+
                       "       apl.vigencia, "+
                       "       apl.sigla,"+
                       "       apl.fecha_crea,"+
                       "       apl.usuario_crea,"+
                       "       apl.terminal_crea,"+
                       "       apl.fecha_modifica,"+
                       "       apl.usuario_modifica,"+
                       "       apl.terminal_modifica,"+
                       "       apl.responsable,"+
                       "       apl.dueno,"+
                       "       apl.descripcion,"+
                       "       apl.usu_codigo_identificado_dueno,"+
                       "       apl.usu_codigo"+
                       " FROM vcs_web_aplicaciones apl";

				String filters = "";
				// Filtro por Nombre
				if ( (criterea.getName() != null) && !(criterea.getName().equalsIgnoreCase("")) ) 
				{
						filters = filters + " UPPER(apl.nombre) LIKE '%" + criterea.getName().toUpperCase() + "%' " ;
				}
				// Filtro por Descripcion
				if ( (criterea.getDescription() != null) && !(criterea.getDescription().equalsIgnoreCase("")) ) 
				{
						if ( filters.equalsIgnoreCase("") ) 
						{
								filters = filters + " UPPER(apl.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%' ";
						}
						else
						{
								filters = filters + " AND UPPER(apl.descripcion) LIKE '%" + criterea.getDescription().toUpperCase() + "%' ";
						}
				}  
				// Filtro por Codigo
				if ( (criterea.getCode() != null) && !(criterea.getCode().longValue()==0) ) 
				{
						if (filters.equalsIgnoreCase("")) 
						{
								filters = filters + " apl.codigo =" + criterea.getCode();
						}
						else
						{
								filters = filters + " AND apl.codigo =" + criterea.getCode();
						}
				}

				// Filtro por Vigencia
				if ( (criterea.getAbbreviation() != null) && !(criterea.getAbbreviation().equals("")) ) 
				{
					if ( filters.equals("") ) 
						{
								filters = filters + " UPPER(apl.sigla) LIKE '%" + criterea.getAbbreviation().toUpperCase() + "%' ";
						}
						else
						{
								filters = filters + " AND UPPER(apl.sigla) LIKE '%" + criterea.getAbbreviation().toUpperCase() + "%' ";
						}
				}
				// Filtro por dueno
				if ( (criterea.getOwnerUserCode() != null) && !(criterea.getOwnerUserCode().longValue()==0) ) 
				{
						if (filters.equalsIgnoreCase("")) 
						{
								filters = filters + " apl.usu_codigo_identificado_dueno =" + criterea.getOwnerUserCode();
						}
						else
						{
								filters = filters + " AND apl.usu_codigo_identificado_dueno =" + criterea.getOwnerUserCode();
						}
				}
				// Si hay filtros, entonces armar la clausula WHERE 
				if ( !filters.equalsIgnoreCase("") ) {
					query = query + " where apl.nombre = 'BENEFICIOS' " + filters;
				}else
        {
          query = query + " where apl.nombre = 'BENEFICIOS' ";
        }

				Statement stm = null;
				ResultSet rst = null;
				ArrayList applications = new ArrayList();
				stm = conn.createStatement();
				rst = stm.executeQuery(query);
				ApplicationVO application = null;
				while ( rst.next() ) 
				{
						application = new ApplicationVO();
						application.setCode(new Long(rst.getLong( "codigo")));      
						application.setName(rst.getString( "nombre"));
						application.setEffectiveFrom(rst.getString( "dvig"));      
						application.setEffectiveUntil(rst.getString("hvig"));      
						application.setUse(rst.getString( "vigencia"));      
						application.setAbbreviation(rst.getString("sigla"));
						application.setCreationDate(rst.getDate("fecha_crea"));
						application.setInChargeName(rst.getString("responsable"));
						application.setOwnerName(rst.getString("dueno"));
            application.setDescription(rst.getString("descripcion"));
            application.setOwnerUserCode(new Long(rst.getLong("usu_codigo_identificado_dueno")));
            application.setUserCode(new Long(rst.getLong("usu_codigo")));
						applications.add(application);
						application = null;
			 }
         
			 stm.close();
			 return applications;
  }

		public ApplicationVO createApplication( ApplicationVO application ) throws SQLException {
  
				String strSQL="";
				boolean result=false;
				PreparedStatement ps=null;
							 strSQL = "" +
								 "INSERT INTO tcs_aplicacion ("     +
								 "            codigo, nombre, descripcion, desde_vigencia, hasta_vigencia, "   +
								 "            vigencia, sigla, usu_codigo, usu_codigo_identificado_dueno,"   +
								 "            usuario_crea,terminal_crea,usuario_modifica, terminal_modifica) "  +
								 "            VALUES(scs_aplicacion.nextval,?,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'),?,?,?,?,?,?,?,?)";
        
				ps = conn.prepareStatement( strSQL );
				ps.setString(1, application.getName().toUpperCase());
				ps.setString(2, application.getDescription().toUpperCase());
				ps.setString(3, application.getEffectiveFrom());
				ps.setString(4, application.getEffectiveUntil());
				ps.setString(5, application.getUse().toUpperCase());
				ps.setString(6, application.getAbbreviation().toUpperCase());
				ps.setLong(7, application.getUserCode().longValue());
				ps.setLong(8, application.getOwnerUserCode().longValue());
				ps.setString(9, application.getCreationUser().toUpperCase());
				ps.setString(10, "WEB=" + application.getCreationTerminal());
				ps.setString(11, application.getUpdateUser().toUpperCase());
				ps.setString(12, "WEB=" + application.getUpdateTerminal());
        
				result = ps.execute();

				

				ps.close();
    
				return application;
		}

    public int updateApplication( ApplicationVO application ) throws SQLException 
		{
    
				String strSQL = "update tcs_aplicacion " +
												"set nombre = ?, " +
												"descripcion = ?, " +
												"desde_vigencia = to_date(?,'yyyy-mm-dd'), " +
												"hasta_vigencia = to_date(?,'yyyy-mm-dd'), " +
												"vigencia = ?, " +
												"sigla = ?, " +
												"usuario_modifica = ?, " +
												"terminal_modifica = ?, " +
												"usu_codigo = ?, " +
												"usu_codigo_identificado_dueno = ? " +
												"where codigo = ?";

				PreparedStatement ps = conn.prepareStatement( strSQL );
				ps.setString(1, application.getName().toUpperCase());
				ps.setString(2, application.getDescription().toUpperCase());
				ps.setString(3, application.getEffectiveFrom());
				ps.setString(4, application.getEffectiveUntil());
				ps.setString(5, application.getUse().toUpperCase());
				ps.setString(6, application.getAbbreviation().toUpperCase());
				ps.setString(7, application.getUpdateUser().toUpperCase());
				ps.setString(8, "WEB=" + application.getUpdateTerminal());
				ps.setLong(9, application.getUserCode().longValue());
				ps.setLong(10, application.getOwnerUserCode().longValue());
				ps.setLong(11, application.getCode().longValue());
  
				int update = ps.executeUpdate();
				ps.close();
				return update;
    
    }

		public int deleteApplication( ApplicationVO application ) throws SQLException 
		{
    
				String strSQL = "delete from tcs_aplicacion where codigo = ?";

				PreparedStatement ps = conn.prepareStatement( strSQL );
				
				ps.setLong(1, application.getCode().longValue());
  
				int update = ps.executeUpdate();
				ps.close();
				return update;
    
    }
  
  
}